/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Yuhan
 */


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.net.*;

import java.util.*;
import DataAccessLayer.CourseInfo;
import DataAccessLayer.DegreeProgram;

public class DataAccessObject {
    private Statement statementObject = null;
    private Connection connectionObject = null;


    public void init(String dbHost, String dbName, String dbUser, String password) throws Exception{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //dbUser =  java.net.URLEncode.encode(dbUser);
            String connString = "jdbc:mysql://"+dbHost+"/"+dbName+"?user="+dbUser+"&password="+password;
            connectionObject = DriverManager.getConnection(connString);
            statementObject = connectionObject.createStatement();
    }

    public void release() throws Exception {
        statementObject.close();
        connectionObject.close();
    }

    public Statement getStatementObject() {
        return statementObject;
    }

    /**
     * 
     * @param department
     *          The department name Example: "CS"
     * @return  Return an array list of CourseInfo objects.
     * @throws Exception
     */
    public ArrayList getCourseInfo(String department) throws Exception {
        String sqlStr = "select * from catalog where courseID like '" +department+ " %'";
         ResultSet rs = statementObject.executeQuery(sqlStr);
        ResultSetMetaData rsMeta = rs.getMetaData();
        int varColCount = rsMeta.getColumnCount();

        ArrayList list = new ArrayList();
        final int COURSE_ID = 0;
        final int TITLE = 1;
        final int UNITS = 2;

        String[] buffer = new String[varColCount];
        while(rs.next()) {
            CourseInfo courseInfo = new CourseInfo();
            for(int col=1;col<=varColCount;col++) {
                buffer[col-1] = rs.getString(col);
            }
            courseInfo.courseId = buffer[COURSE_ID];
            courseInfo.title = buffer[TITLE];
            courseInfo.units = Integer.parseInt(buffer[UNITS]);
            list.add(courseInfo);
        }

        return list;
    }


    public DegreeProgram getDegreeProgram(String major, String degree) throws Exception
    {
        String sqlStr = "select courseId, type  from degreeprogram where (degree = '"+degree+" ' AND major= '"+major+"') or type like 'GE%';";
        ResultSet rs = statementObject.executeQuery(sqlStr);
        ResultSetMetaData rsMeta = rs.getMetaData();
        int varColCount = rsMeta.getColumnCount();
        String[] buffer = new String[varColCount];
        Hashtable classesTable = new Hashtable();

        while(rs.next()) {
            String courseId = rs.getString(1);
            String type = rs.getString(2);
            ArrayList classList;
            if(classesTable.containsKey(type)) {
                classList = (ArrayList) classesTable.get(type);
            } else {
                classList = new ArrayList();
                classesTable.put(type, classList);
            }
            classList.add(courseId);
        }

        return new DegreeProgram(major, degree, classesTable);
    }


    public static void main(String[] args) throws Exception {
        DataAccessObject dao = new DataAccessObject();
        String dbHost = "68.178.254.44";
        String dbName = "hcidb" ;
        String dbUser = "hcidb";
        String dbPassword = "Testdb123";

        //dal.init(dbHost, dbName, dbUser, dbPassword);
        dao.init("localhost", "ClassAutoPlanner", "root", "");

        ArrayList list = dao.getCourseInfo("CS");
        for(int i=0;i<list.size();i++) {
            CourseInfo ci = (CourseInfo) list.get(i);
            System.out.println(ci.title);
        }

        DegreeProgram dp = dao.getDegreeProgram("CS", "BS");
        System.out.println("Number of core classes: " + dp.getClassList("CORE").length);
        dp = dao.getDegreeProgram("PHYSICS", "BS");
        System.out.println("Number of core classes: " + dp.getClassList("CORE").length);

        dao.release();
    }
}